package com.bw.dao;

import com.bw.doman.sugar;
import com.bw.doman.sugarTwo;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;

import java.util.List;

@Mapper
public interface sugarDao {
    @Select("SELECT b.name,sum(o.final_total_amount) value FROM order_info o \n" +
            "LEFT JOIN base_province b ON o.province_id=b.id\n" +
            "GROUP BY b.`name`")
    List<sugar> getList();


    @Select("SELECT  t.tm_name name,sum(final_total_amount) value FROM order_info o \n" +
            "LEFT JOIN order_detail d on d.order_id=o.id\n" +
            "LEFT JOIN sku_info s on d.sku_id=s.id\n" +
            "LEFT JOIN base_trademark t on s.tm_id=t.tm_id\n" +
            "GROUP BY t.tm_name")
    List<sugar> getList2();


    @Select("SELECT  c1.name,sum(final_total_amount) value FROM order_info o \n" +
            "LEFT JOIN order_detail d on d.order_id=o.id\n" +
            "LEFT JOIN sku_info s on d.sku_id=s.id\n" +
            "LEFT JOIN base_category3 c3 on s.category3_id=c3.id\n" +
            "LEFT JOIN base_category2 c2 on c3.category2_id=c2.id\n" +
            "LEFT JOIN base_category1 c1 on c2.category1_id=c1.id\n" +
            "GROUP BY c1.`name`")
    List<sugar> getList3();

    @Select("SELECT * FROM\n" +
            "(SELECT count(*) `olduse` FROM user_info WHERE TIMESTAMPDIFF(month,create_time,NOW())>=3)a,\n" +
            "(SELECT count(*) `newuse` FROM user_info WHERE TIMESTAMPDIFF(month,create_time,NOW())<3)b")
    List<sugarTwo> getList4();
}
